import pandas as pd
import numpy as np
from sklearn.ensemble import RandomForestRegressor, RandomForestClassifier
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import StandardScaler
import mysql.connector
from datetime import datetime, timedelta
import logging
import joblib
import os
from pathlib import Path
import random
from sqlalchemy import create_engine
import warnings

# 过滤掉pandas的警告信息
warnings.filterwarnings('ignore', category=UserWarning)

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

class LotteryPredictor:
    def __init__(self):
        # 使用SQLAlchemy创建数据库连接
        self.engine = create_engine('mysql+mysqlconnector://root:Duchenyi619@localhost/redAndBlue')
        self.connection = self.engine.raw_connection()
        
        # 模型文件路径
        self.model_dir = Path('models')
        self.model_dir.mkdir(exist_ok=True)
        self.red_models_path = self.model_dir / 'red_models.pkl'
        self.blue_model_path = self.model_dir / 'blue_model.pkl'
        self.last_train_date_path = self.model_dir / 'last_train_date.txt'
        
        # 尝试加载已有模型，如果没有则创建新模型
        if self._load_models():
            logging.info("成功加载已有模型")
        else:
            logging.info("创建新模型")
            self._create_new_models()
    
    def _create_new_models(self):
        """创建新的模型实例"""
        self.red_models = [RandomForestRegressor(
            n_estimators=200,
            max_depth=10,
            min_samples_split=5,
            random_state=i
        ) for i in range(6)]
        
        self.blue_model = RandomForestClassifier(
            n_estimators=200,
            max_depth=10,
            min_samples_split=5,
            random_state=42
        )
    
    def _load_models(self):
        """尝试加载已保存的模型"""
        try:
            if self.red_models_path.exists() and self.blue_model_path.exists():
                self.red_models = joblib.load(self.red_models_path)
                self.blue_model = joblib.load(self.blue_model_path)
                return True
            return False
        except Exception as e:
            logging.error(f"加载模型失败: {e}")
            return False
    
    def _save_models(self):
        """保存模型到文件"""
        try:
            joblib.dump(self.red_models, self.red_models_path)
            joblib.dump(self.blue_model, self.blue_model_path)
            logging.info("模型保存成功")
        except Exception as e:
            logging.error(f"保存模型失败: {e}")
    
    def _save_last_train_date(self, last_date):
        """保存最后训练日期"""
        try:
            with open(self.last_train_date_path, 'w') as f:
                f.write(last_date.strftime('%Y-%m-%d'))
        except Exception as e:
            logging.error(f"保存训练日期失败: {e}")
    
    def _get_last_train_date(self):
        """获取最后训练日期"""
        try:
            if self.last_train_date_path.exists():
                with open(self.last_train_date_path, 'r') as f:
                    date_str = f.read().strip()
                    return pd.to_datetime(date_str)
            return None
        except Exception as e:
            logging.error(f"读取训练日期失败: {e}")
            return None
    
    def needs_retraining(self):
        """检查是否需要重新训练模型"""
        try:
            # 获取数据库中最新一期的日期
            query = "SELECT MAX(draw_date) as latest_date FROM lottery_data"
            df = pd.read_sql(query, self.engine)
            latest_date = pd.to_datetime(df['latest_date'].iloc[0])
            
            # 获取上次训练时的最新日期
            last_train_date = self._get_last_train_date()
            
            # 如果没有训练记录或有新数据，需要重新训练
            if last_train_date is None or latest_date > last_train_date:
                logging.info(f"发现新数据，需要重新训练 (上次训练: {last_train_date}, 最新数据: {latest_date})")
                return True
                
            logging.info("无新数据，使用现有模型")
            return False
            
        except Exception as e:
            logging.error(f"检查训练状态失败: {e}")
            return True  # 出错时保险起见重新训练

    def prepare_features(self, df):
        """准备特征数据"""
        features = []
        current_time = pd.Timestamp.now()
        
        # 获取蓝球历史频率
        blue_freq_query = """
        SELECT blue_ball, COUNT(*) as frequency
        FROM lottery_data
        GROUP BY blue_ball
        ORDER BY blue_ball
        """
        blue_freq_df = pd.read_sql(blue_freq_query, self.engine)
        blue_freq_dict = dict(zip(blue_freq_df['blue_ball'], blue_freq_df['frequency']))
        
        # 获取每个蓝球上次出现的日期
        blue_last_appear_query = """
        SELECT blue_ball, MAX(draw_date) as last_appear
        FROM lottery_data
        GROUP BY blue_ball
        """
        blue_last_appear_df = pd.read_sql(blue_last_appear_query, self.engine)
        blue_last_appear_dict = dict(zip(blue_last_appear_df['blue_ball'], 
                                       pd.to_datetime(blue_last_appear_df['last_appear'])))
        
        for i in range(len(df)):
            row = df.iloc[i]
            
            # 确保draw_date是datetime类型
            draw_date = pd.to_datetime(row['draw_date'])
            # 计算与当前日期的时间差（天数）
            days_diff = (current_time - draw_date).days
            # 计算时间权重（越近的数据权重越大）
            time_weight = np.exp(-days_diff / 365)  # 使用一年作为衰减周期
            
            # 计算蓝球相关特征
            current_blue = row['blue_ball']
            blue_freq = blue_freq_dict.get(current_blue, 0)
            blue_last_appear = blue_last_appear_dict.get(current_blue, draw_date)
            days_since_last_appear = (draw_date - blue_last_appear).days
            
            # 新增时间特征
            season = (draw_date.month - 1) // 3 + 1  # 季节(1-4)
            is_month_start = 1 if draw_date.day <= 3 else 0  # 是否月初
            is_month_end = 1 if draw_date.day >= 28 else 0  # 是否月末
            is_quarter_start = 1 if draw_date.day <= 3 and draw_date.month in [1,4,7,10] else 0  # 是否季度初
            is_quarter_end = 1 if draw_date.day >= 28 and draw_date.month in [3,6,9,12] else 0  # 是否季度末
            
            feature = {
                # 基本统计特征
                'red_sum': sum([row[f'red_ball_{j}'] for j in range(1, 7)]),
                'red_mean': np.mean([row[f'red_ball_{j}'] for j in range(1, 7)]),
                'red_std': np.std([row[f'red_ball_{j}'] for j in range(1, 7)]),
                
                # 号码分布特征
                'red_odd_count': sum(1 for j in range(1, 7) if row[f'red_ball_{j}'] % 2 == 1),
                'red_even_count': sum(1 for j in range(1, 7) if row[f'red_ball_{j}'] % 2 == 0),
                'red_prime_count': sum(1 for j in range(1, 7) if self._is_prime(row[f'red_ball_{j}'])),
                
                # 范围特征
                'red_max': max([row[f'red_ball_{j}'] for j in range(1, 7)]),
                'red_min': min([row[f'red_ball_{j}'] for j in range(1, 7)]),
                'red_range': max([row[f'red_ball_{j}'] for j in range(1, 7)]) - 
                           min([row[f'red_ball_{j}'] for j in range(1, 7)]),
                
                # 区间分布
                'red_small': sum(1 for j in range(1, 7) if row[f'red_ball_{j}'] <= 11),
                'red_medium': sum(1 for j in range(1, 7) if 11 < row[f'red_ball_{j}'] <= 22),
                'red_large': sum(1 for j in range(1, 7) if row[f'red_ball_{j}'] > 22),
                
                # 连号特征
                'consecutive_count': self._count_consecutive_numbers([row[f'red_ball_{j}'] for j in range(1, 7)]),
                
                # 蓝球特征
                'blue_is_odd': current_blue % 2,
                'blue_is_prime': self._is_prime(current_blue),
                'blue_normalized': current_blue / 16,
                'blue_small': 1 if current_blue <= 8 else 0,
                'blue_frequency': blue_freq,
                'blue_days_since_last_appear': days_since_last_appear,
                'blue_weekday': draw_date.weekday(),
                'blue_month': draw_date.month,
                
                # 时间特征
                'time_weight': time_weight,
                'year': draw_date.year,
                'month': draw_date.month,
                'day_of_week': draw_date.weekday(),
                'season': season,
                'is_month_start': is_month_start,
                'is_month_end': is_month_end,
                'is_quarter_start': is_quarter_start,
                'is_quarter_end': is_quarter_end
            }
            features.append(feature)
        return pd.DataFrame(features)

    def _count_consecutive_numbers(self, numbers):
        """统计连号数量"""
        numbers = sorted(numbers)
        count = 0
        for i in range(len(numbers)-1):
            if numbers[i] + 1 == numbers[i+1]:
                count += 1
        return count

    def analyze_historical_patterns(self):
        """分析历史数据模式"""
        query = "SELECT * FROM lottery_data ORDER BY draw_date DESC"
        df = pd.read_sql_query(query, self.connection)
        
        # 确保draw_date列是datetime类型
        df['draw_date'] = pd.to_datetime(df['draw_date'])
        
        print("\n历史数据分析:")
        print("-" * 60)
        print(f"总数据量: {len(df)} 期")
        print(f"数据时间范围: {df['draw_date'].min().date()} 至 {df['draw_date'].max().date()}")
        
        # 分析不同时期的数据
        current_time = pd.Timestamp.now()
        recent_year = df[df['draw_date'] >= current_time - pd.DateOffset(years=1)]
        recent_5_years = df[df['draw_date'] >= current_time - pd.DateOffset(years=5)]
        
        # 1. 红球频率分析（按时期）
        print("\n红球出现频率分析:")
        print("最近一年:")
        self._analyze_period(recent_year)
        print("\n最近五年:")
        self._analyze_period(recent_5_years)
        print("\n全部历史:")
        self._analyze_period(df)
        
        # 2. 趋势分析
        print("\n数字趋势分析:")
        self._analyze_trends(df)
        
        return df

    def _analyze_period(self, df):
        """分析特定时期的数据"""
        red_numbers = []
        for i in range(1, 7):
            red_numbers.extend(df[f'red_ball_{i}'].tolist())
        
        red_freq = pd.Series(red_numbers).value_counts().sort_index()
        blue_freq = df['blue_ball'].value_counts().sort_index()
        
        print(f"期数: {len(df)}")
        print("热门红球 (前5名):")
        print(red_freq.nlargest(5))
        print("热门蓝球 (前3名):")
        print(blue_freq.nlargest(3))
        
        # 计算奇偶比例
        odd_ratio = sum(x % 2 == 1 for x in red_numbers) / len(red_numbers)
        print(f"红球奇偶比: {odd_ratio:.2%} (奇数) vs {1-odd_ratio:.2%} (偶数)")

    def _analyze_trends(self, df):
        """分析数字趋势"""
        # 按年份分组分析
        df['year'] = df['draw_date'].dt.year
        yearly_stats = df.groupby('year').agg({
            'red_ball_1': ['mean', 'std'],
            'blue_ball': ['mean', 'std']
        })
        print("\n年度统计:")
        print(yearly_stats.tail())

    def _is_prime(self, n):
        """判断是否为质数"""
        if n < 2:
            return False
        for i in range(2, int(np.sqrt(n)) + 1):
            if n % i == 0:
                return False
        return True

    def evaluate_model_performance(self, test_data, predictions):
        """评估模型性能"""
        try:
            # 计算红球预测准确率
            red_accuracy = []
            for i in range(6):
                actual = test_data[f'red_ball_{i+1}'].values
                pred = predictions[i]
                accuracy = np.mean(np.abs(actual - pred) <= 1)  # 允许1个数字的误差
                red_accuracy.append(accuracy)
            
            # 计算蓝球预测准确率
            actual_blue = test_data['blue_ball'].values
            pred_blue = predictions[6]
            blue_accuracy = np.mean(actual_blue == pred_blue)
            
            # 计算综合准确率
            overall_accuracy = np.mean(red_accuracy + [blue_accuracy])
            
            # 计算预测分数分布
            scores = [self._score_prediction(pred[:6], pred[6]) for pred in predictions.T]
            avg_score = np.mean(scores)
            score_std = np.std(scores)
            
            # 记录评估结果
            logging.info("\n模型性能评估:")
            logging.info("-" * 50)
            logging.info(f"红球预测准确率: {np.mean(red_accuracy):.2%}")
            for i, acc in enumerate(red_accuracy, 1):
                logging.info(f"  红球{i}准确率: {acc:.2%}")
            logging.info(f"蓝球预测准确率: {blue_accuracy:.2%}")
            logging.info(f"综合准确率: {overall_accuracy:.2%}")
            logging.info(f"平均预测分数: {avg_score:.2f}")
            logging.info(f"预测分数标准差: {score_std:.2f}")
            
            return {
                'red_accuracy': red_accuracy,
                'blue_accuracy': blue_accuracy,
                'overall_accuracy': overall_accuracy,
                'avg_score': avg_score,
                'score_std': score_std
            }
            
        except Exception as e:
            logging.error(f"评估模型性能时出错: {e}")
            return None

    def analyze_prediction_accuracy(self, n_periods=10):
        """分析历史预测准确性"""
        try:
            # 获取最近n期的实际开奖结果
            query = f"""
            SELECT * FROM lottery_data 
            ORDER BY draw_date DESC 
            LIMIT {n_periods}
            """
            actual_results = pd.read_sql(query, self.engine)
            
            # 获取对应的预测结果
            query = f"""
            SELECT * FROM lottery_predictions 
            WHERE prediction_date IN (
                SELECT draw_date FROM lottery_data 
                ORDER BY draw_date DESC 
                LIMIT {n_periods}
            )
            ORDER BY prediction_date DESC
            """
            predictions = pd.read_sql(query, self.engine)
            
            accuracy_stats = {
                'red_matches': [],  # 红球匹配数
                'blue_matches': [],  # 蓝球匹配数
                'scores': [],  # 预测分数
                'rankings': []  # 预测排名
            }
            
            # 分析每期预测
            for _, actual in actual_results.iterrows():
                # 获取该期的预测结果
                period_predictions = predictions[predictions['prediction_date'] == actual['draw_date']]
                if period_predictions.empty:
                    continue
                
                # 计算实际号码
                actual_red = [actual[f'red_ball_{i}'] for i in range(1, 7)]
                actual_blue = actual['blue_ball']
                
                # 分析每个预测
                for _, pred in period_predictions.iterrows():
                    # 解析预测号码
                    pred_red = [int(x) for x in pred['red_balls'].split(',')]
                    pred_blue = pred['blue_ball']
                    
                    # 计算匹配数
                    red_matches = len(set(actual_red) & set(pred_red))
                    blue_match = actual_blue == pred_blue
                    
                    accuracy_stats['red_matches'].append(red_matches)
                    accuracy_stats['blue_matches'].append(blue_match)
                    accuracy_stats['scores'].append(pred['score'])
                    accuracy_stats['rankings'].append(pred['ranking'])
            
            # 计算统计指标
            avg_red_matches = np.mean(accuracy_stats['red_matches'])
            blue_accuracy = np.mean(accuracy_stats['blue_matches'])
            avg_score = np.mean(accuracy_stats['scores'])
            avg_ranking = np.mean(accuracy_stats['rankings'])
            
            # 记录分析结果
            logging.info("\n历史预测准确性分析:")
            logging.info("-" * 50)
            logging.info(f"分析期数: {n_periods}")
            logging.info(f"平均红球匹配数: {avg_red_matches:.2f}")
            logging.info(f"蓝球预测准确率: {blue_accuracy:.2%}")
            logging.info(f"平均预测分数: {avg_score:.2f}")
            logging.info(f"平均预测排名: {avg_ranking:.2f}")
            
            return accuracy_stats
            
        except Exception as e:
            logging.error(f"分析预测准确性时出错: {e}")
            return None

    def train_and_evaluate(self):
        """训练模型并评估准确性"""
        # 获取所有历史数据
        query = """
        SELECT * FROM lottery_data 
        ORDER BY draw_date DESC
        """
        df = pd.read_sql(query, self.engine)
        
        # 检查是否需要重新训练
        if not self.needs_retraining():
            logging.info("使用现有模型进行预测...")
            # 显示现有模型的特征重要性
            features = self.prepare_features(df)
            feature_names = features.columns.tolist()
            logging.info(f"特征数量: {len(feature_names)}")
            
            # 显示红球模型的特征重要性
            for i, model in enumerate(self.red_models):
                if hasattr(model, 'feature_importances_'):
                    feature_importance = pd.DataFrame({
                        'feature': feature_names,
                        'importance': model.feature_importances_
                    }).sort_values('importance', ascending=False)
                    logging.info(f"\n红球{i+1}模型的重要特征:")
                    logging.info(feature_importance.head())
            
            # 显示蓝球模型的特征重要性
            if hasattr(self.blue_model, 'feature_importances_'):
                blue_feature_importance = pd.DataFrame({
                    'feature': feature_names,
                    'importance': self.blue_model.feature_importances_
                }).sort_values('importance', ascending=False)
                logging.info("\n蓝球模型的重要特征:")
                logging.info(blue_feature_importance.head())
            
            return None, None
        
        # 需要重新训练
        logging.info("开始重新训练模型...")
        
        # 分析历史模式
        self.analyze_historical_patterns()
        
        # 保留最近一期数据用于测试
        test_data = df.iloc[0:1]
        train_data = df.iloc[1:]
        
        # 准备训练数据
        X_train = self.prepare_features(train_data)
        feature_names = X_train.columns.tolist()
        logging.info(f"特征数量: {len(feature_names)}")
        sample_weights = X_train['time_weight'].values
        y_red_train = [train_data[f'red_ball_{i}'].values for i in range(1, 7)]
        y_blue_train = train_data['blue_ball'].values
        
        # 训练模型
        for i, model in enumerate(self.red_models):
            model.fit(X_train, y_red_train[i], sample_weight=sample_weights)
            if hasattr(model, 'feature_importances_'):
                feature_importance = pd.DataFrame({
                    'feature': feature_names,
                    'importance': model.feature_importances_
                }).sort_values('importance', ascending=False)
                logging.info(f"\n红球{i+1}模型的重要特征:")
                logging.info(feature_importance.head())
        
        self.blue_model.fit(X_train, y_blue_train, sample_weight=sample_weights)
        
        # 显示蓝球模型的特征重要性
        if hasattr(self.blue_model, 'feature_importances_'):
            blue_feature_importance = pd.DataFrame({
                'feature': feature_names,
                'importance': self.blue_model.feature_importances_
            }).sort_values('importance', ascending=False)
            logging.info("\n蓝球模型的重要特征:")
            logging.info(blue_feature_importance.head())
        
        # 评估模型性能
        X_test = self.prepare_features(test_data)
        predictions = []
        for i, model in enumerate(self.red_models):
            pred = model.predict(X_test)
            predictions.append(pred)
        
        blue_pred = self.blue_model.predict(X_test)
        predictions.append(blue_pred)
        
        performance = self.evaluate_model_performance(test_data, np.array(predictions))
        
        # 分析历史预测准确性
        accuracy_stats = self.analyze_prediction_accuracy()
        
        # 保存模型和训练日期
        self._save_models()
        self._save_last_train_date(df['draw_date'].max())
        
        return performance, accuracy_stats

    def _save_prediction_to_db(self, red_balls, blue_ball, prediction_date, score):
        """
        保存预测结果到数据库
        :param red_balls: 红球列表
        :param blue_ball: 蓝球
        :param prediction_date: 预测日期
        :param score: 预测分数
        """
        # 插入新的预测结果
        insert_query = """
        INSERT INTO lottery_predictions 
        (red_balls, blue_ball, prediction_date, created_at, updated_at, score) 
        VALUES (%s, %s, %s, NOW(), NOW(), %s)
        """
        
        red_balls_str = ','.join(map(str, red_balls))
        cursor = self.connection.cursor()
        cursor.execute(insert_query, (red_balls_str, blue_ball, prediction_date, score))
        
        self.connection.commit()
        cursor.close()
        logging.info(f"成功保存预测结果到数据库，预测日期: {prediction_date}")

    def analyze_number_correlations(self):
        """分析号码之间的关联性"""
        try:
            # 获取最近100期数据
            query = "SELECT * FROM lottery_data ORDER BY draw_date DESC LIMIT 100"
            df = pd.read_sql(query, self.engine)
            
            correlations = {
                'consecutive_pairs': [],  # 连号对
                'sum_pairs': [],         # 和值对
                'diff_pairs': [],        # 差值对
                'ratio_pairs': [],       # 比值对
                'complement_pairs': []   # 互补对
            }
            
            # 分析每期号码
            for _, row in df.iterrows():
                red_balls = [row[f'red_ball_{i}'] for i in range(1, 7)]
                red_balls.sort()
                
                # 1. 分析连号对
                for i in range(len(red_balls)-1):
                    if red_balls[i+1] - red_balls[i] == 1:
                        correlations['consecutive_pairs'].append((red_balls[i], red_balls[i+1]))
                
                # 2. 分析和值对
                for i in range(len(red_balls)):
                    for j in range(i+1, len(red_balls)):
                        sum_value = red_balls[i] + red_balls[j]
                        if 20 <= sum_value <= 40:  # 合理的和值范围
                            correlations['sum_pairs'].append((red_balls[i], red_balls[j]))
                
                # 3. 分析差值对
                for i in range(len(red_balls)):
                    for j in range(i+1, len(red_balls)):
                        diff_value = red_balls[j] - red_balls[i]
                        if 2 <= diff_value <= 10:  # 合理的差值范围
                            correlations['diff_pairs'].append((red_balls[i], red_balls[j]))
                
                # 4. 分析比值对
                for i in range(len(red_balls)):
                    for j in range(i+1, len(red_balls)):
                        ratio_value = red_balls[j] / red_balls[i]
                        if 1.2 <= ratio_value <= 2.5:  # 合理的比值范围
                            correlations['ratio_pairs'].append((red_balls[i], red_balls[j]))
                
                # 5. 分析互补对
                for i in range(len(red_balls)):
                    for j in range(i+1, len(red_balls)):
                        if red_balls[i] + red_balls[j] == 34:  # 互补数对
                            correlations['complement_pairs'].append((red_balls[i], red_balls[j]))
            
            # 计算每种关联性的频率
            for key in correlations:
                if correlations[key]:
                    freq = pd.Series(correlations[key]).value_counts()
                    correlations[key] = freq.to_dict()
            
            logging.info("\n号码关联性分析结果:")
            logging.info("-" * 50)
            for key, value in correlations.items():
                if value:
                    logging.info(f"\n{key}:")
                    for pair, count in value.items():
                        logging.info(f"  {pair}: {count}次")
            
            return correlations
            
        except Exception as e:
            logging.error(f"分析号码关联性时出错: {e}")
            return None

    def _score_prediction(self, red_balls, blue_ball):
        """对预测结果进行评分"""
        score = 0
        try:
            # 获取号码关联性分析结果
            correlations = self.analyze_number_correlations()
            if correlations:
                # 10. 关联性分析（新增）
                red_balls_sorted = sorted(red_balls)
                
                # 检查连号对
                consecutive_count = sum(1 for i in range(len(red_balls_sorted)-1) 
                                     if red_balls_sorted[i+1] - red_balls_sorted[i] == 1)
                if consecutive_count <= 2:  # 适度的连号
                    score += 4
                
                # 检查和值对
                sum_pairs = correlations['sum_pairs']
                valid_sum_pairs = sum(1 for i in range(len(red_balls_sorted))
                                    for j in range(i+1, len(red_balls_sorted))
                                    if (red_balls_sorted[i], red_balls_sorted[j]) in sum_pairs)
                if valid_sum_pairs >= 2:  # 至少2对合理的和值对
                    score += 4
                
                # 检查差值对
                diff_pairs = correlations['diff_pairs']
                valid_diff_pairs = sum(1 for i in range(len(red_balls_sorted))
                                     for j in range(i+1, len(red_balls_sorted))
                                     if (red_balls_sorted[i], red_balls_sorted[j]) in diff_pairs)
                if valid_diff_pairs >= 2:  # 至少2对合理的差值对
                    score += 4
                
                # 检查互补对
                complement_pairs = correlations['complement_pairs']
                valid_complement_pairs = sum(1 for i in range(len(red_balls_sorted))
                                          for j in range(i+1, len(red_balls_sorted))
                                          if (red_balls_sorted[i], red_balls_sorted[j]) in complement_pairs)
                if valid_complement_pairs >= 1:  # 至少1对互补对
                    score += 4
            
            # 原有的评分逻辑
            # 1. 历史频率分析（调整权重）
            query = """
            SELECT number, COUNT(*) as frequency
            FROM (
                SELECT red_ball_1 as number FROM lottery_data
                UNION ALL SELECT red_ball_2 FROM lottery_data
                UNION ALL SELECT red_ball_3 FROM lottery_data
                UNION ALL SELECT red_ball_4 FROM lottery_data
                UNION ALL SELECT red_ball_5 FROM lottery_data
                UNION ALL SELECT red_ball_6 FROM lottery_data
            ) as numbers
            GROUP BY number
            ORDER BY frequency DESC
            """
            df = pd.read_sql_query(query, self.connection)
            hot_numbers = df['number'].tolist()[:15]  # 获取历史出现频率最高的15个号码
            
            # 计算预测号码中热门号码的数量
            hot_count = sum(1 for ball in red_balls if ball in hot_numbers)
            score += hot_count * 4  # 每个热门号码加4分
            
            # 2. 奇偶平衡（调整权重）
            odd_count = sum(1 for ball in red_balls if ball % 2 == 1)
            even_count = 6 - odd_count
            balance_score = min(odd_count, even_count)
            score += balance_score * 3  # 奇偶越平衡分数越高
            
            # 3. 号码分布（调整权重）
            small = sum(1 for ball in red_balls if ball <= 11)
            medium = sum(1 for ball in red_balls if 11 < ball <= 22)
            large = sum(1 for ball in red_balls if ball > 22)
            distribution_score = min(small, medium, large)
            score += distribution_score * 3  # 分布越均匀分数越高
            
            # 4. 连号分析（调整权重）
            consecutive_count = self._count_consecutive_numbers(red_balls)
            if consecutive_count <= 2:  # 适度的连号（0-2个）
                score += 6  # 增加连号得分
            
            # 5. 蓝球分析（调整权重）
            blue_query = "SELECT blue_ball, COUNT(*) as frequency FROM lottery_data GROUP BY blue_ball ORDER BY frequency DESC"
            blue_df = pd.read_sql_query(blue_query, self.connection)
            hot_blue_numbers = blue_df['blue_ball'].tolist()[:5]  # 获取最常出现的5个蓝球
            if blue_ball in hot_blue_numbers:
                score += 4  # 增加蓝球得分
            
            # 6. 号码间隔分析（调整权重）
            red_balls_sorted = sorted(red_balls)
            gaps = [red_balls_sorted[i+1] - red_balls_sorted[i] for i in range(len(red_balls_sorted)-1)]
            avg_gap = sum(gaps) / len(gaps)
            if 3 <= avg_gap <= 6:  # 合理的号码间隔
                score += 4  # 增加间隔得分
            
            # 7. 质数分析（调整权重）
            prime_count = sum(1 for ball in red_balls if self._is_prime(ball))
            if 2 <= prime_count <= 4:  # 合理的质数数量
                score += 4  # 增加质数得分
            
            # 8. 和值分析（调整权重）
            red_sum = sum(red_balls)
            if 70 <= red_sum <= 100:  # 合理的和值范围
                score += 4  # 增加和值得分
            
            # 9. 跨度分析（调整权重）
            red_span = max(red_balls) - min(red_balls)
            if 15 <= red_span <= 25:  # 合理的跨度范围
                score += 4  # 增加跨度得分
            
            return score
            
        except Exception as e:
            logging.error(f"计算预测分数时出错: {e}")
            return 0

    def predict_next(self, n_predictions=5, features=None, save_to_db=True):
        """
        预测下一期号码
        :param n_predictions: 预测次数
        :param features: 特征数据
        :param save_to_db: 是否保存到数据库
        :return: 预测结果列表
        """
        if features is None:
            # 获取历史数据
            features = self.prepare_features(pd.read_sql_query("SELECT * FROM lottery_data ORDER BY draw_date DESC", self.connection))
        
        all_predictions = []
        for _ in range(n_predictions):
            # 预测红球
            red_balls = []
            for i in range(6):
                pred = self.red_models[i].predict(features.iloc[0].values.reshape(1, -1))[0]
                red_balls.append(int(round(pred)))
            
            # 预测蓝球
            blue_pred = self._predict_blue_ball()
            
            # 确保预测的号码在有效范围内
            red_balls = [max(1, min(33, x)) for x in red_balls]
            blue_ball = max(1, min(16, blue_pred))
            
            # 对红球进行排序和去重
            red_balls = sorted(list(set(red_balls)))
            
            # 如果红球数量不足6个，随机补充
            while len(red_balls) < 6:
                new_num = random.randint(1, 33)
                if new_num not in red_balls:
                    red_balls.append(new_num)
            red_balls.sort()
            
            # 计算预测分数
            score = self._score_prediction(red_balls, blue_ball)
            all_predictions.append((red_balls, blue_ball, score))
        
        # 按分数排序
        all_predictions.sort(key=lambda x: x[2], reverse=True)
        
        if save_to_db:
            # 获取前5组预测
            best_predictions = all_predictions[:5]
            next_draw_date = self._get_next_draw_date()
            
            # 保存预测结果
            for red_balls, blue_ball, score in best_predictions:
                self._save_prediction_to_db(red_balls, blue_ball, next_draw_date, score)
            
            # 打印预测结果
            print("\n下一期预测号码 (按可能性从高到低排序):")
            print("-" * 60)
            for i, (red_balls, blue_ball, score) in enumerate(best_predictions, 1):
                print(f"预测 {i} (评分: {score}):")
                print(f"红球: {' '.join(map(str, red_balls))}")
                print(f"蓝球: {blue_ball}")
                print("-" * 60)
        
        return all_predictions

    def get_best_predictions(self):
        """获取得分最高的5组预测号码"""
        predictions = []
        max_iterations = 10  # 保持10次迭代
        iteration = 0
        
        logging.info("开始生成预测号码...")
        
        # 获取历史数据分布
        query = "SELECT * FROM lottery_data ORDER BY draw_date DESC LIMIT 100"
        df = pd.read_sql(query, self.engine)
        
        # 计算历史红球分布
        red_distribution = []
        for i in range(1, 7):
            red_distribution.append(df[f'red_ball_{i}'].value_counts(normalize=True).to_dict())
        
        # 计算历史蓝球分布
        blue_distribution = df['blue_ball'].value_counts(normalize=True).to_dict()
        
        while iteration < max_iterations:
            iteration += 1
            if iteration % 2 == 0:
                logging.info(f"已完成 {iteration} 次迭代，当前已生成 {len(predictions)} 组预测")
            
            # 生成新的预测组合
            red_balls = []
            used_numbers = set()
            
            # 使用历史分布和随机性生成红球
            for i in range(6):
                while True:
                    # 结合模型预测和历史分布
                    model_pred = self._predict_red_ball(i)
                    historical_dist = red_distribution[i]
                    
                    # 计算每个号码的权重
                    weights = []
                    numbers = []
                    for num in range(1, 34):
                        if num not in used_numbers:
                            hist_weight = historical_dist.get(num, 0.01)  # 使用0.01作为默认值
                            model_weight = 1.0 if num == model_pred else 0.1
                            weights.append(hist_weight * model_weight)
                            numbers.append(num)
                    
                    # 归一化权重
                    weights = np.array(weights) / sum(weights)
                    
                    # 根据权重随机选择号码
                    selected_num = np.random.choice(numbers, p=weights)
                    if selected_num not in used_numbers:
                        red_balls.append(selected_num)
                        used_numbers.add(selected_num)
                        break
            
            # 对红球排序
            red_balls.sort()
            
            # 预测蓝球
            blue_ball = self._predict_blue_ball()
            
            # 计算得分
            score = self._score_prediction(red_balls, blue_ball)
            
            prediction = {
                'red_balls': red_balls,
                'blue_ball': blue_ball,
                'score': score
            }
            
            # 每次迭代都添加预测结果
            predictions.append(prediction)
            logging.info(f"第 {iteration} 次迭代生成预测组合，得分: {score}")
        
        # 按得分排序
        predictions.sort(key=lambda x: x['score'], reverse=True)
        
        # 选择得分最高的5组预测
        best_predictions = predictions[:5]
        
        logging.info(f"预测完成，共生成 {len(predictions)} 组预测，选择得分最高的5组")
        
        # 删除下期开奖日期的旧预测
        next_draw_date = self._get_next_draw_date()
        cursor = self.connection.cursor()
        cursor.execute("DELETE FROM lottery_predictions WHERE prediction_date = %s", (next_draw_date,))
        self.connection.commit()
        
        # 保存新的预测结果
        for prediction in best_predictions:
            self._save_prediction_to_db(prediction['red_balls'], prediction['blue_ball'], next_draw_date, prediction['score'])
        
        return best_predictions

    def _get_next_draw_date(self):
        """获取下一期开奖日期"""
        query = "SELECT MAX(draw_date) as latest_date FROM lottery_data"
        df = pd.read_sql(query, self.engine)
        latest_date = pd.to_datetime(df['latest_date'].iloc[0])
        
        # 计算下一期日期（假设每周二、四、日开奖）
        next_date = latest_date
        while True:
            next_date += timedelta(days=1)
            if next_date.weekday() in [1, 3, 6]:  # 周二(1)、周四(3)、周日(6)
                break
        return next_date

    def get_historical_weights(self):
        """获取历史号码出现频率作为权重"""
        try:
            query = """
            SELECT number, COUNT(*) as frequency
            FROM (
                SELECT red_ball_1 as number FROM lottery_data
                UNION ALL
                SELECT red_ball_2 FROM lottery_data
                UNION ALL
                SELECT red_ball_3 FROM lottery_data
                UNION ALL
                SELECT red_ball_4 FROM lottery_data
                UNION ALL
                SELECT red_ball_5 FROM lottery_data
                UNION ALL
                SELECT red_ball_6 FROM lottery_data
            ) as numbers
            GROUP BY number
            ORDER BY number
            """
            df = pd.read_sql(query, self.engine)
            frequencies = df['frequency'].values
            # 计算权重
            weights = frequencies / frequencies.sum()
            return weights
        except:
            # 如果查询失败，返回均匀分布的权重
            return np.ones(33) / 33

    def evaluate_prediction(self, actual, predicted):
        """评估预测结果"""
        red_matches = len(set(actual['red_balls']) & set(predicted['red_balls']))
        blue_match = actual['blue_ball'] == predicted['blue_ball']
        return red_matches, blue_match

    def _predict_red_ball(self, position):
        """预测指定位置的红球号码"""
        # 获取历史数据
        query = "SELECT * FROM lottery_data ORDER BY draw_date DESC"
        df = pd.read_sql(query, self.engine)
        features = self.prepare_features(df)
        
        # 使用对应位置的模型进行预测
        pred = self.red_models[position].predict(features.iloc[[0]])
        pred = max(1, min(33, int(pred[0])))
        return pred

    def _predict_blue_ball(self):
        """预测蓝球号码"""
        # 获取历史数据
        query = "SELECT * FROM lottery_data ORDER BY draw_date DESC"
        df = pd.read_sql(query, self.engine)
        
        # 随机选择一行历史数据
        random_idx = np.random.randint(0, min(100, len(df)))  # 从最近100期中随机选择
        random_data = df.iloc[[random_idx]]
        features = self.prepare_features(random_data)
        
        # 获取历史频率
        blue_freq_query = """
        SELECT blue_ball, COUNT(*) as frequency
        FROM lottery_data
        GROUP BY blue_ball
        ORDER BY blue_ball
        """
        blue_freq_df = pd.read_sql(blue_freq_query, self.engine)
        blue_freq_dict = dict(zip(blue_freq_df['blue_ball'], blue_freq_df['frequency']))
        
        # 获取模型预测概率
        pred_proba = self.blue_model.predict_proba(features)[0]
        
        # 结合模型预测概率和历史频率，降低历史频率的权重
        weighted_proba = pred_proba * (np.array([blue_freq_dict.get(i+1, 1) for i in range(16)]) ** 0.5)
        
        # 增加随机性
        random_factor = np.random.normal(0, 0.3, size=16)  # 增加随机扰动的标准差
        weighted_proba = weighted_proba + random_factor
        weighted_proba = np.maximum(0, weighted_proba)  # 确保概率非负
        
        # 归一化概率
        weighted_proba = weighted_proba / weighted_proba.sum()
        
        # 打印详细的概率信息
        logging.info("\n蓝球预测概率详情:")
        logging.info("-" * 50)
        for i in range(16):
            ball_num = i + 1
            model_prob = pred_proba[i]
            hist_freq = blue_freq_dict.get(ball_num, 1)
            final_prob = weighted_proba[i]
            logging.info(f"蓝球 {ball_num:02d}: 模型概率={model_prob:.4f}, 历史频率={hist_freq:.4f}, 最终概率={final_prob:.4f}")
        logging.info("-" * 50)
        
        # 根据概率随机选择号码
        pred = np.random.choice(range(1, 17), p=weighted_proba)
        selected_ball = max(1, min(16, int(pred)))
        logging.info(f"最终选择的蓝球: {selected_ball:02d}")
        
        return selected_ball

    def generate_happy8_numbers(self, n_groups=5, n_choices=10):
        """生成快乐8的选10号码"""
        all_numbers = list(range(1, 81))  # 快乐8的号码范围是1到80
        all_predictions = []
        
        for _ in range(n_groups):
            # 随机选择10个号码
            selected_numbers = random.sample(all_numbers, n_choices)
            selected_numbers.sort()  # 排序
            all_predictions.append(selected_numbers)
        
        return all_predictions

    def insert_new_lottery_data(self, period, draw_date, red_balls, blue_ball):
        """插入新的开奖数据"""
        try:
            # 确保数据库连接是活跃的
            if not self.connection.is_connected():
                logging.info("重新连接数据库...")
                self.connection = self.engine.raw_connection()
            
            cursor = self.connection.cursor()
            
            # 检查该期数是否已存在
            check_query = "SELECT COUNT(*) FROM lottery_data WHERE period = %s"
            logging.info(f"检查期数 {period} 是否存在...")
            cursor.execute(check_query, (period,))
            count = cursor.fetchone()[0]
            
            if count > 0:
                logging.info(f"期数 {period} 已存在，跳过插入")
                cursor.close()
                return False
            
            # 插入新数据
            insert_query = """
            INSERT INTO lottery_data 
            (period, draw_date, red_ball_1, red_ball_2, red_ball_3, red_ball_4, red_ball_5, red_ball_6, blue_ball, created_at, updated_at)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW())
            """
            
            # 准备数据
            data = (period, draw_date) + tuple(red_balls) + (blue_ball,)
            logging.info(f"准备插入数据: {data}")
            
            # 执行插入
            cursor.execute(insert_query, data)
            self.connection.commit()
            cursor.close()
            
            logging.info(f"成功插入期数 {period} 的开奖数据")
            return True
            
        except mysql.connector.Error as e:
            logging.error(f"MySQL错误: {e}")
            if cursor:
                cursor.close()
            return False
        except Exception as e:
            logging.error(f"插入新数据时出错: {e}")
            if cursor:
                cursor.close()
            return False

    def update_model_with_new_data(self):
        """使用最新数据更新模型"""
        try:
            # 获取所有历史数据
            query = "SELECT * FROM lottery_data ORDER BY draw_date DESC"
            df = pd.read_sql(query, self.engine)
            
            # 准备训练数据
            features = self.prepare_features(df)
            
            # 训练红球模型
            for i in range(6):
                y = df[f'red_ball_{i+1}'].values
                self.red_models[i].fit(features, y)
            
            # 训练蓝球模型
            y_blue = df['blue_ball'].values
            self.blue_model.fit(features, y_blue)
            
            # 保存模型
            self._save_models()
            self._save_last_train_date(df['draw_date'].max())
            
            logging.info("模型更新成功")
            return True
            
        except Exception as e:
            logging.error(f"更新模型时出错: {e}")
            return False

def main():
    predictor = LotteryPredictor()
    
    # 插入最新一期数据
    new_period = "25025"
    draw_date = "2024-03-19"  # 假设今天是2024-03-19
    red_balls = [12, 15, 21, 23, 25, 30]
    blue_ball = 2
    
    print("\n🔄 更新开奖数据...")
    print(f"期数: {new_period}")
    print(f"开奖日期: {draw_date}")
    print(f"红球: {' '.join(map(str, red_balls))}")
    print(f"蓝球: {blue_ball}")
    
    if predictor.insert_new_lottery_data(new_period, draw_date, red_balls, blue_ball):
        print("✅ 新数据插入成功")
        print("\n🔄 更新预测模型...")
        if predictor.update_model_with_new_data():
            print("✅ 模型更新成功")
        else:
            print("❌ 模型更新失败")
    else:
        print("❌ 新数据插入失败")
        print("请检查以下可能的问题：")
        print("1. 数据库连接是否正常")
        print("2. 数据库表结构是否正确")
        print("3. 该期数是否已经存在")
        print("4. 数据格式是否正确")
        return  # 如果插入失败，直接返回
    
    # 获取最佳预测
    print("\n🎯 生成新预测...")
    best_predictions = predictor.get_best_predictions()
    
    print("\n📊 预测结果:")
    print("-" * 70)
    for i, pred in enumerate(best_predictions, 1):
        print(f"\n预测 {i} (评分: {pred['score']}分)")
        print(f"红球: {' '.join(f'{x:02d}' for x in pred['red_balls'])}")
        print(f"蓝球: {pred['blue_ball']:02d}")
    
    print("\n💡 选号建议:")
    print("-" * 70)
    print("1. 建议优先考虑评分较高的前2-3组号码")
    print("2. 每组号码都经过机器学习模型和规则引擎双重优化")
    print("3. 号码特征（和值、跨度、间隔等）都在合理范围内")
    
    print("\n⚠️ 温馨提示:")
    print("-" * 70)
    print("1. 本预测基于机器学习模型和统计分析，仅供参考")
    print("2. 双色球本质上是随机事件，任何预测都不能保证中奖")
    print("3. 请理性购彩，注意风险")
    print("=" * 70)

if __name__ == "__main__":
    main() 